
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
  <head>
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Custom Lookups &#8212; Django 1.11.22.dev20190603194737 documentation</title>
    <link rel="stylesheet" href="../_static/default.css" type="text/css" />
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
    <script type="text/javascript" src="../_static/jquery.js"></script>
    <script type="text/javascript" src="../_static/underscore.js"></script>
    <script type="text/javascript" src="../_static/doctools.js"></script>
    <script type="text/javascript" src="../_static/language_data.js"></script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="Custom template tags and filters" href="custom-template-tags.html" />
    <link rel="prev" title="Writing custom model fields" href="custom-model-fields.html" />



 
<script type="text/javascript" src="../templatebuiltins.js"></script>
<script type="text/javascript">
(function($) {
    if (!django_template_builtins) {
       // templatebuiltins.js missing, do nothing.
       return;
    }
    $(document).ready(function() {
        // Hyperlink Django template tags and filters
        var base = "../ref/templates/builtins.html";
        if (base == "#") {
            // Special case for builtins.html itself
            base = "";
        }
        // Tags are keywords, class '.k'
        $("div.highlight\\-html\\+django span.k").each(function(i, elem) {
             var tagname = $(elem).text();
             if ($.inArray(tagname, django_template_builtins.ttags) != -1) {
                 var fragment = tagname.replace(/_/, '-');
                 $(elem).html("<a href='" + base + "#" + fragment + "'>" + tagname + "</a>");
             }
        });
        // Filters are functions, class '.nf'
        $("div.highlight\\-html\\+django span.nf").each(function(i, elem) {
             var filtername = $(elem).text();
             if ($.inArray(filtername, django_template_builtins.tfilters) != -1) {
                 var fragment = filtername.replace(/_/, '-');
                 $(elem).html("<a href='" + base + "#" + fragment + "'>" + filtername + "</a>");
             }
        });
    });
})(jQuery);
</script>


  </head><body>

    <div class="document">
  <div id="custom-doc" class="yui-t6">
    <div id="hd">
      <h1><a href="../index.html">Django 1.11.22.dev20190603194737 documentation</a></h1>
      <div id="global-nav">
        <a title="Home page" href="../index.html">Home</a>  |
        <a title="Table of contents" href="../contents.html">Table of contents</a>  |
        <a title="Global index" href="../genindex.html">Index</a>  |
        <a title="Module index" href="../py-modindex.html">Modules</a>
      </div>
      <div class="nav">
    &laquo; <a href="custom-model-fields.html" title="Writing custom model fields">previous</a>
     |
    <a href="index.html" title="“How-to” guides" accesskey="U">up</a>
   |
    <a href="custom-template-tags.html" title="Custom template tags and filters">next</a> &raquo;</div>
    </div>

    <div id="bd">
      <div id="yui-main">
        <div class="yui-b">
          <div class="yui-g" id="howto-custom-lookups">
            
  <div class="section" id="s-custom-lookups">
<span id="custom-lookups"></span><h1>Custom Lookups<a class="headerlink" href="#custom-lookups" title="Permalink to this headline">¶</a></h1>
<p>Django offers a wide variety of <a class="reference internal" href="../ref/models/querysets.html#field-lookups"><span class="std std-ref">built-in lookups</span></a> for
filtering (for example, <code class="docutils literal notranslate"><span class="pre">exact</span></code> and <code class="docutils literal notranslate"><span class="pre">icontains</span></code>). This documentation
explains how to write custom lookups and how to alter the working of existing
lookups. For the API references of lookups, see the <a class="reference internal" href="../ref/models/lookups.html"><span class="doc">Lookup API reference</span></a>.</p>
<div class="section" id="s-a-simple-lookup-example">
<span id="a-simple-lookup-example"></span><h2>A simple lookup example<a class="headerlink" href="#a-simple-lookup-example" title="Permalink to this headline">¶</a></h2>
<p>Let’s start with a simple custom lookup. We will write a custom lookup <code class="docutils literal notranslate"><span class="pre">ne</span></code>
which works opposite to <code class="docutils literal notranslate"><span class="pre">exact</span></code>. <code class="docutils literal notranslate"><span class="pre">Author.objects.filter(name__ne='Jack')</span></code>
will translate to the SQL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="s2">&quot;author&quot;</span><span class="o">.</span><span class="s2">&quot;name&quot;</span> <span class="o">&lt;&gt;</span> <span class="s1">&#39;Jack&#39;</span>
</pre></div>
</div>
<p>This SQL is backend independent, so we don’t need to worry about different
databases.</p>
<p>There are two steps to making this work. Firstly we need to implement the
lookup, then we need to tell Django about it. The implementation is quite
straightforward:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">Lookup</span>

<span class="k">class</span> <span class="nc">NotEqual</span><span class="p">(</span><span class="n">Lookup</span><span class="p">):</span>
    <span class="n">lookup_name</span> <span class="o">=</span> <span class="s1">&#39;ne&#39;</span>

    <span class="k">def</span> <span class="nf">as_sql</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">):</span>
        <span class="n">lhs</span><span class="p">,</span> <span class="n">lhs_params</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">process_lhs</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">)</span>
        <span class="n">rhs</span><span class="p">,</span> <span class="n">rhs_params</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">process_rhs</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">)</span>
        <span class="n">params</span> <span class="o">=</span> <span class="n">lhs_params</span> <span class="o">+</span> <span class="n">rhs_params</span>
        <span class="k">return</span> <span class="s1">&#39;</span><span class="si">%s</span><span class="s1"> &lt;&gt; </span><span class="si">%s</span><span class="s1">&#39;</span> <span class="o">%</span> <span class="p">(</span><span class="n">lhs</span><span class="p">,</span> <span class="n">rhs</span><span class="p">),</span> <span class="n">params</span>
</pre></div>
</div>
<p>To register the <code class="docutils literal notranslate"><span class="pre">NotEqual</span></code> lookup we will just need to call
<code class="docutils literal notranslate"><span class="pre">register_lookup</span></code> on the field class we want the lookup to be available. In
this case, the lookup makes sense on all <code class="docutils literal notranslate"><span class="pre">Field</span></code> subclasses, so we register
it with <code class="docutils literal notranslate"><span class="pre">Field</span></code> directly:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models.fields</span> <span class="k">import</span> <span class="n">Field</span>
<span class="n">Field</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">NotEqual</span><span class="p">)</span>
</pre></div>
</div>
<p>Lookup registration can also be done using a decorator pattern:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models.fields</span> <span class="k">import</span> <span class="n">Field</span>

<span class="nd">@Field</span><span class="o">.</span><span class="n">register_lookup</span>
<span class="k">class</span> <span class="nc">NotEqualLookup</span><span class="p">(</span><span class="n">Lookup</span><span class="p">):</span>
    <span class="c1"># ...</span>
</pre></div>
</div>
<p>We can now use <code class="docutils literal notranslate"><span class="pre">foo__ne</span></code> for any field <code class="docutils literal notranslate"><span class="pre">foo</span></code>. You will need to ensure that
this registration happens before you try to create any querysets using it. You
could place the implementation in a <code class="docutils literal notranslate"><span class="pre">models.py</span></code> file, or register the lookup
in the <code class="docutils literal notranslate"><span class="pre">ready()</span></code> method of an <code class="docutils literal notranslate"><span class="pre">AppConfig</span></code>.</p>
<p>Taking a closer look at the implementation, the first required attribute is
<code class="docutils literal notranslate"><span class="pre">lookup_name</span></code>. This allows the ORM to understand how to interpret <code class="docutils literal notranslate"><span class="pre">name__ne</span></code>
and use <code class="docutils literal notranslate"><span class="pre">NotEqual</span></code> to generate the SQL. By convention, these names are always
lowercase strings containing only letters, but the only hard requirement is
that it must not contain the string <code class="docutils literal notranslate"><span class="pre">__</span></code>.</p>
<p>We then need to define the <code class="docutils literal notranslate"><span class="pre">as_sql</span></code> method. This takes a <code class="docutils literal notranslate"><span class="pre">SQLCompiler</span></code>
object, called <code class="docutils literal notranslate"><span class="pre">compiler</span></code>,  and the active database connection.
<code class="docutils literal notranslate"><span class="pre">SQLCompiler</span></code> objects are not documented, but the only thing we need to know
about them is that they have a <code class="docutils literal notranslate"><span class="pre">compile()</span></code> method which returns a tuple
containing an SQL string, and the parameters to be interpolated into that
string. In most cases, you don’t need to use it directly and can pass it on to
<code class="docutils literal notranslate"><span class="pre">process_lhs()</span></code> and <code class="docutils literal notranslate"><span class="pre">process_rhs()</span></code>.</p>
<p>A <code class="docutils literal notranslate"><span class="pre">Lookup</span></code> works against two values, <code class="docutils literal notranslate"><span class="pre">lhs</span></code> and <code class="docutils literal notranslate"><span class="pre">rhs</span></code>, standing for
left-hand side and right-hand side. The left-hand side is usually a field
reference, but it can be anything implementing the <a class="reference internal" href="../ref/models/lookups.html#query-expression"><span class="std std-ref">query expression API</span></a>. The right-hand is the value given by the user. In the
example <code class="docutils literal notranslate"><span class="pre">Author.objects.filter(name__ne='Jack')</span></code>, the left-hand side is a
reference to the <code class="docutils literal notranslate"><span class="pre">name</span></code> field of the <code class="docutils literal notranslate"><span class="pre">Author</span></code> model, and <code class="docutils literal notranslate"><span class="pre">'Jack'</span></code> is the
right-hand side.</p>
<p>We call <code class="docutils literal notranslate"><span class="pre">process_lhs</span></code> and <code class="docutils literal notranslate"><span class="pre">process_rhs</span></code> to convert them into the values we
need for SQL using the <code class="docutils literal notranslate"><span class="pre">compiler</span></code> object described before. These methods
return tuples containing some SQL and the parameters to be interpolated into
that SQL, just as we need to return from our <code class="docutils literal notranslate"><span class="pre">as_sql</span></code> method. In the above
example, <code class="docutils literal notranslate"><span class="pre">process_lhs</span></code> returns <code class="docutils literal notranslate"><span class="pre">('&quot;author&quot;.&quot;name&quot;',</span> <span class="pre">[])</span></code> and
<code class="docutils literal notranslate"><span class="pre">process_rhs</span></code> returns <code class="docutils literal notranslate"><span class="pre">('&quot;%s&quot;',</span> <span class="pre">['Jack'])</span></code>. In this example there were no
parameters for the left hand side, but this would depend on the object we have,
so we still need to include them in the parameters we return.</p>
<p>Finally we combine the parts into an SQL expression with <code class="docutils literal notranslate"><span class="pre">&lt;&gt;</span></code>, and supply all
the parameters for the query. We then return a tuple containing the generated
SQL string and the parameters.</p>
</div>
<div class="section" id="s-a-simple-transformer-example">
<span id="a-simple-transformer-example"></span><h2>A simple transformer example<a class="headerlink" href="#a-simple-transformer-example" title="Permalink to this headline">¶</a></h2>
<p>The custom lookup above is great, but in some cases you may want to be able to
chain lookups together. For example, let’s suppose we are building an
application where we want to make use of the <code class="docutils literal notranslate"><span class="pre">abs()</span></code> operator.
We have an <code class="docutils literal notranslate"><span class="pre">Experiment</span></code> model which records a start value, end value, and the
change (start - end). We would like to find all experiments where the change
was equal to a certain amount (<code class="docutils literal notranslate"><span class="pre">Experiment.objects.filter(change__abs=27)</span></code>),
or where it did not exceed a certain amount
(<code class="docutils literal notranslate"><span class="pre">Experiment.objects.filter(change__abs__lt=27)</span></code>).</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">This example is somewhat contrived, but it nicely demonstrates the range of
functionality which is possible in a database backend independent manner,
and without duplicating functionality already in Django.</p>
</div>
<p>We will start by writing a <code class="docutils literal notranslate"><span class="pre">AbsoluteValue</span></code> transformer. This will use the SQL
function <code class="docutils literal notranslate"><span class="pre">ABS()</span></code> to transform the value before comparison:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">Transform</span>

<span class="k">class</span> <span class="nc">AbsoluteValue</span><span class="p">(</span><span class="n">Transform</span><span class="p">):</span>
    <span class="n">lookup_name</span> <span class="o">=</span> <span class="s1">&#39;abs&#39;</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;ABS&#39;</span>
</pre></div>
</div>
<p>Next, let’s register it for <code class="docutils literal notranslate"><span class="pre">IntegerField</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">IntegerField</span>
<span class="n">IntegerField</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">AbsoluteValue</span><span class="p">)</span>
</pre></div>
</div>
<p>We can now run the queries we had before.
<code class="docutils literal notranslate"><span class="pre">Experiment.objects.filter(change__abs=27)</span></code> will generate the following SQL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="o">...</span> <span class="n">WHERE</span> <span class="n">ABS</span><span class="p">(</span><span class="s2">&quot;experiments&quot;</span><span class="o">.</span><span class="s2">&quot;change&quot;</span><span class="p">)</span> <span class="o">=</span> <span class="mi">27</span>
</pre></div>
</div>
<p>By using <code class="docutils literal notranslate"><span class="pre">Transform</span></code> instead of <code class="docutils literal notranslate"><span class="pre">Lookup</span></code> it means we are able to chain
further lookups afterwards. So
<code class="docutils literal notranslate"><span class="pre">Experiment.objects.filter(change__abs__lt=27)</span></code> will generate the following
SQL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="o">...</span> <span class="n">WHERE</span> <span class="n">ABS</span><span class="p">(</span><span class="s2">&quot;experiments&quot;</span><span class="o">.</span><span class="s2">&quot;change&quot;</span><span class="p">)</span> <span class="o">&lt;</span> <span class="mi">27</span>
</pre></div>
</div>
<p>Note that in case there is no other lookup specified, Django interprets
<code class="docutils literal notranslate"><span class="pre">change__abs=27</span></code> as <code class="docutils literal notranslate"><span class="pre">change__abs__exact=27</span></code>.</p>
<p>When looking for which lookups are allowable after the <code class="docutils literal notranslate"><span class="pre">Transform</span></code> has been
applied, Django uses the <code class="docutils literal notranslate"><span class="pre">output_field</span></code> attribute. We didn’t need to specify
this here as it didn’t change, but supposing we were applying <code class="docutils literal notranslate"><span class="pre">AbsoluteValue</span></code>
to some field which represents a more complex type (for example a point
relative to an origin, or a complex number) then we may have wanted to specify
that the transform returns a <code class="docutils literal notranslate"><span class="pre">FloatField</span></code> type for further lookups. This can
be done by adding an <code class="docutils literal notranslate"><span class="pre">output_field</span></code> attribute to the transform:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">FloatField</span><span class="p">,</span> <span class="n">Transform</span>

<span class="k">class</span> <span class="nc">AbsoluteValue</span><span class="p">(</span><span class="n">Transform</span><span class="p">):</span>
    <span class="n">lookup_name</span> <span class="o">=</span> <span class="s1">&#39;abs&#39;</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;ABS&#39;</span>

    <span class="nd">@property</span>
    <span class="k">def</span> <span class="nf">output_field</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
        <span class="k">return</span> <span class="n">FloatField</span><span class="p">()</span>
</pre></div>
</div>
<p>This ensures that further lookups like <code class="docutils literal notranslate"><span class="pre">abs__lte</span></code> behave as they would for
a <code class="docutils literal notranslate"><span class="pre">FloatField</span></code>.</p>
</div>
<div class="section" id="s-writing-an-efficient-abs-lt-lookup">
<span id="writing-an-efficient-abs-lt-lookup"></span><h2>Writing an efficient <code class="docutils literal notranslate"><span class="pre">abs__lt</span></code> lookup<a class="headerlink" href="#writing-an-efficient-abs-lt-lookup" title="Permalink to this headline">¶</a></h2>
<p>When using the above written <code class="docutils literal notranslate"><span class="pre">abs</span></code> lookup, the SQL produced will not use
indexes efficiently in some cases. In particular, when we use
<code class="docutils literal notranslate"><span class="pre">change__abs__lt=27</span></code>, this is equivalent to <code class="docutils literal notranslate"><span class="pre">change__gt=-27</span></code> AND
<code class="docutils literal notranslate"><span class="pre">change__lt=27</span></code>. (For the <code class="docutils literal notranslate"><span class="pre">lte</span></code> case we could use the SQL <code class="docutils literal notranslate"><span class="pre">BETWEEN</span></code>).</p>
<p>So we would like <code class="docutils literal notranslate"><span class="pre">Experiment.objects.filter(change__abs__lt=27)</span></code> to generate
the following SQL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="o">..</span> <span class="n">WHERE</span> <span class="s2">&quot;experiments&quot;</span><span class="o">.</span><span class="s2">&quot;change&quot;</span> <span class="o">&lt;</span> <span class="mi">27</span> <span class="n">AND</span> <span class="s2">&quot;experiments&quot;</span><span class="o">.</span><span class="s2">&quot;change&quot;</span> <span class="o">&gt;</span> <span class="o">-</span><span class="mi">27</span>
</pre></div>
</div>
<p>The implementation is:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">Lookup</span>

<span class="k">class</span> <span class="nc">AbsoluteValueLessThan</span><span class="p">(</span><span class="n">Lookup</span><span class="p">):</span>
    <span class="n">lookup_name</span> <span class="o">=</span> <span class="s1">&#39;lt&#39;</span>

    <span class="k">def</span> <span class="nf">as_sql</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">):</span>
        <span class="n">lhs</span><span class="p">,</span> <span class="n">lhs_params</span> <span class="o">=</span> <span class="n">compiler</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">lhs</span><span class="o">.</span><span class="n">lhs</span><span class="p">)</span>
        <span class="n">rhs</span><span class="p">,</span> <span class="n">rhs_params</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">process_rhs</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">)</span>
        <span class="n">params</span> <span class="o">=</span> <span class="n">lhs_params</span> <span class="o">+</span> <span class="n">rhs_params</span> <span class="o">+</span> <span class="n">lhs_params</span> <span class="o">+</span> <span class="n">rhs_params</span>
        <span class="k">return</span> <span class="s1">&#39;</span><span class="si">%s</span><span class="s1"> &lt; </span><span class="si">%s</span><span class="s1"> AND </span><span class="si">%s</span><span class="s1"> &gt; -</span><span class="si">%s</span><span class="s1">&#39;</span> <span class="o">%</span> <span class="p">(</span><span class="n">lhs</span><span class="p">,</span> <span class="n">rhs</span><span class="p">,</span> <span class="n">lhs</span><span class="p">,</span> <span class="n">rhs</span><span class="p">),</span> <span class="n">params</span>

<span class="n">AbsoluteValue</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">AbsoluteValueLessThan</span><span class="p">)</span>
</pre></div>
</div>
<p>There are a couple of notable things going on. First, <code class="docutils literal notranslate"><span class="pre">AbsoluteValueLessThan</span></code>
isn’t calling <code class="docutils literal notranslate"><span class="pre">process_lhs()</span></code>. Instead it skips the transformation of the
<code class="docutils literal notranslate"><span class="pre">lhs</span></code> done by <code class="docutils literal notranslate"><span class="pre">AbsoluteValue</span></code> and uses the original <code class="docutils literal notranslate"><span class="pre">lhs</span></code>. That is, we
want to get <code class="docutils literal notranslate"><span class="pre">&quot;experiments&quot;.&quot;change&quot;</span></code> not <code class="docutils literal notranslate"><span class="pre">ABS(&quot;experiments&quot;.&quot;change&quot;)</span></code>.
Referring directly to <code class="docutils literal notranslate"><span class="pre">self.lhs.lhs</span></code> is safe as <code class="docutils literal notranslate"><span class="pre">AbsoluteValueLessThan</span></code>
can be accessed only from the <code class="docutils literal notranslate"><span class="pre">AbsoluteValue</span></code> lookup, that is the <code class="docutils literal notranslate"><span class="pre">lhs</span></code>
is always an instance of <code class="docutils literal notranslate"><span class="pre">AbsoluteValue</span></code>.</p>
<p>Notice also that  as both sides are used multiple times in the query the params
need to contain <code class="docutils literal notranslate"><span class="pre">lhs_params</span></code> and <code class="docutils literal notranslate"><span class="pre">rhs_params</span></code> multiple times.</p>
<p>The final query does the inversion (<code class="docutils literal notranslate"><span class="pre">27</span></code> to <code class="docutils literal notranslate"><span class="pre">-27</span></code>) directly in the
database. The reason for doing this is that if the <code class="docutils literal notranslate"><span class="pre">self.rhs</span></code> is something else
than a plain integer value (for example an <code class="docutils literal notranslate"><span class="pre">F()</span></code> reference) we can’t do the
transformations in Python.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">In fact, most lookups with <code class="docutils literal notranslate"><span class="pre">__abs</span></code> could be implemented as range queries
like this, and on most database backends it is likely to be more sensible to
do so as you can make use of the indexes. However with PostgreSQL you may
want to add an index on <code class="docutils literal notranslate"><span class="pre">abs(change)</span></code> which would allow these queries to
be very efficient.</p>
</div>
</div>
<div class="section" id="s-a-bilateral-transformer-example">
<span id="a-bilateral-transformer-example"></span><h2>A bilateral transformer example<a class="headerlink" href="#a-bilateral-transformer-example" title="Permalink to this headline">¶</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">AbsoluteValue</span></code> example we discussed previously is a transformation which
applies to the left-hand side of the lookup. There may be some cases where you
want the transformation to be applied to both the left-hand side and the
right-hand side. For instance, if you want to filter a queryset based on the
equality of the left and right-hand side insensitively to some SQL function.</p>
<p>Let’s examine the simple example of case-insensitive transformation here. This
transformation isn’t very useful in practice as Django already comes with a bunch
of built-in case-insensitive lookups, but it will be a nice demonstration of
bilateral transformations in a database-agnostic way.</p>
<p>We define an <code class="docutils literal notranslate"><span class="pre">UpperCase</span></code> transformer which uses the SQL function <code class="docutils literal notranslate"><span class="pre">UPPER()</span></code> to
transform the values before comparison. We define
<a class="reference internal" href="../ref/models/lookups.html#django.db.models.Transform.bilateral" title="django.db.models.Transform.bilateral"><code class="xref py py-attr docutils literal notranslate"><span class="pre">bilateral</span> <span class="pre">=</span> <span class="pre">True</span></code></a> to indicate that
this transformation should apply to both <code class="docutils literal notranslate"><span class="pre">lhs</span></code> and <code class="docutils literal notranslate"><span class="pre">rhs</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">Transform</span>

<span class="k">class</span> <span class="nc">UpperCase</span><span class="p">(</span><span class="n">Transform</span><span class="p">):</span>
    <span class="n">lookup_name</span> <span class="o">=</span> <span class="s1">&#39;upper&#39;</span>
    <span class="n">function</span> <span class="o">=</span> <span class="s1">&#39;UPPER&#39;</span>
    <span class="n">bilateral</span> <span class="o">=</span> <span class="kc">True</span>
</pre></div>
</div>
<p>Next, let’s register it:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">django.db.models</span> <span class="k">import</span> <span class="n">CharField</span><span class="p">,</span> <span class="n">TextField</span>
<span class="n">CharField</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">UpperCase</span><span class="p">)</span>
<span class="n">TextField</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">UpperCase</span><span class="p">)</span>
</pre></div>
</div>
<p>Now, the queryset <code class="docutils literal notranslate"><span class="pre">Author.objects.filter(name__upper=&quot;doe&quot;)</span></code> will generate a case
insensitive query like this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="o">...</span> <span class="n">WHERE</span> <span class="n">UPPER</span><span class="p">(</span><span class="s2">&quot;author&quot;</span><span class="o">.</span><span class="s2">&quot;name&quot;</span><span class="p">)</span> <span class="o">=</span> <span class="n">UPPER</span><span class="p">(</span><span class="s1">&#39;doe&#39;</span><span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="s-writing-alternative-implementations-for-existing-lookups">
<span id="writing-alternative-implementations-for-existing-lookups"></span><h2>Writing alternative implementations for existing lookups<a class="headerlink" href="#writing-alternative-implementations-for-existing-lookups" title="Permalink to this headline">¶</a></h2>
<p>Sometimes different database vendors require different SQL for the same
operation. For this example we will rewrite a custom implementation for
MySQL for the NotEqual operator. Instead of <code class="docutils literal notranslate"><span class="pre">&lt;&gt;</span></code> we will be using <code class="docutils literal notranslate"><span class="pre">!=</span></code>
operator. (Note that in reality almost all databases support both, including
all the official databases supported by Django).</p>
<p>We can change the behavior on a specific backend by creating a subclass of
<code class="docutils literal notranslate"><span class="pre">NotEqual</span></code> with a <code class="docutils literal notranslate"><span class="pre">as_mysql</span></code> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MySQLNotEqual</span><span class="p">(</span><span class="n">NotEqual</span><span class="p">):</span>
    <span class="k">def</span> <span class="nf">as_mysql</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">):</span>
        <span class="n">lhs</span><span class="p">,</span> <span class="n">lhs_params</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">process_lhs</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">)</span>
        <span class="n">rhs</span><span class="p">,</span> <span class="n">rhs_params</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">process_rhs</span><span class="p">(</span><span class="n">compiler</span><span class="p">,</span> <span class="n">connection</span><span class="p">)</span>
        <span class="n">params</span> <span class="o">=</span> <span class="n">lhs_params</span> <span class="o">+</span> <span class="n">rhs_params</span>
        <span class="k">return</span> <span class="s1">&#39;</span><span class="si">%s</span><span class="s1"> != </span><span class="si">%s</span><span class="s1">&#39;</span> <span class="o">%</span> <span class="p">(</span><span class="n">lhs</span><span class="p">,</span> <span class="n">rhs</span><span class="p">),</span> <span class="n">params</span>

<span class="n">Field</span><span class="o">.</span><span class="n">register_lookup</span><span class="p">(</span><span class="n">MySQLNotEqual</span><span class="p">)</span>
</pre></div>
</div>
<p>We can then register it with <code class="docutils literal notranslate"><span class="pre">Field</span></code>. It takes the place of the original
<code class="docutils literal notranslate"><span class="pre">NotEqual</span></code> class as it has the same <code class="docutils literal notranslate"><span class="pre">lookup_name</span></code>.</p>
<p>When compiling a query, Django first looks for <code class="docutils literal notranslate"><span class="pre">as_%s</span> <span class="pre">%</span> <span class="pre">connection.vendor</span></code>
methods, and then falls back to <code class="docutils literal notranslate"><span class="pre">as_sql</span></code>. The vendor names for the in-built
backends are <code class="docutils literal notranslate"><span class="pre">sqlite</span></code>, <code class="docutils literal notranslate"><span class="pre">postgresql</span></code>, <code class="docutils literal notranslate"><span class="pre">oracle</span></code> and <code class="docutils literal notranslate"><span class="pre">mysql</span></code>.</p>
</div>
<div class="section" id="s-how-django-determines-the-lookups-and-transforms-which-are-used">
<span id="how-django-determines-the-lookups-and-transforms-which-are-used"></span><h2>How Django determines the lookups and transforms which are used<a class="headerlink" href="#how-django-determines-the-lookups-and-transforms-which-are-used" title="Permalink to this headline">¶</a></h2>
<p>In some cases you may wish to dynamically change which <code class="docutils literal notranslate"><span class="pre">Transform</span></code> or
<code class="docutils literal notranslate"><span class="pre">Lookup</span></code> is returned based on the name passed in, rather than fixing it. As
an example, you could have a field which stores coordinates or an arbitrary
dimension, and wish to allow a syntax like <code class="docutils literal notranslate"><span class="pre">.filter(coords__x7=4)</span></code> to return
the objects where the 7th coordinate has value 4. In order to do this, you
would override <code class="docutils literal notranslate"><span class="pre">get_lookup</span></code> with something like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">CoordinatesField</span><span class="p">(</span><span class="n">Field</span><span class="p">):</span>
    <span class="k">def</span> <span class="nf">get_lookup</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">lookup_name</span><span class="p">):</span>
        <span class="k">if</span> <span class="n">lookup_name</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">&#39;x&#39;</span><span class="p">):</span>
            <span class="k">try</span><span class="p">:</span>
                <span class="n">dimension</span> <span class="o">=</span> <span class="nb">int</span><span class="p">(</span><span class="n">lookup_name</span><span class="p">[</span><span class="mi">1</span><span class="p">:])</span>
            <span class="k">except</span> <span class="ne">ValueError</span><span class="p">:</span>
                <span class="k">pass</span>
            <span class="k">else</span><span class="p">:</span>
                <span class="k">return</span> <span class="n">get_coordinate_lookup</span><span class="p">(</span><span class="n">dimension</span><span class="p">)</span>
        <span class="k">return</span> <span class="nb">super</span><span class="p">(</span><span class="n">CoordinatesField</span><span class="p">,</span> <span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="n">get_lookup</span><span class="p">(</span><span class="n">lookup_name</span><span class="p">)</span>
</pre></div>
</div>
<p>You would then define <code class="docutils literal notranslate"><span class="pre">get_coordinate_lookup</span></code> appropriately to return a
<code class="docutils literal notranslate"><span class="pre">Lookup</span></code> subclass which handles the relevant value of <code class="docutils literal notranslate"><span class="pre">dimension</span></code>.</p>
<p>There is a similarly named method called <code class="docutils literal notranslate"><span class="pre">get_transform()</span></code>. <code class="docutils literal notranslate"><span class="pre">get_lookup()</span></code>
should always return a <code class="docutils literal notranslate"><span class="pre">Lookup</span></code> subclass, and <code class="docutils literal notranslate"><span class="pre">get_transform()</span></code> a
<code class="docutils literal notranslate"><span class="pre">Transform</span></code> subclass. It is important to remember that <code class="docutils literal notranslate"><span class="pre">Transform</span></code>
objects can be further filtered on, and <code class="docutils literal notranslate"><span class="pre">Lookup</span></code> objects cannot.</p>
<p>When filtering, if there is only one lookup name remaining to be resolved, we
will look for a <code class="docutils literal notranslate"><span class="pre">Lookup</span></code>. If there are multiple names, it will look for a
<code class="docutils literal notranslate"><span class="pre">Transform</span></code>. In the situation where there is only one name and a <code class="docutils literal notranslate"><span class="pre">Lookup</span></code>
is not found, we look for a <code class="docutils literal notranslate"><span class="pre">Transform</span></code> and then the <code class="docutils literal notranslate"><span class="pre">exact</span></code> lookup on that
<code class="docutils literal notranslate"><span class="pre">Transform</span></code>. All call sequences always end with a <code class="docutils literal notranslate"><span class="pre">Lookup</span></code>. To clarify:</p>
<ul class="simple">
<li><code class="docutils literal notranslate"><span class="pre">.filter(myfield__mylookup)</span></code> will call <code class="docutils literal notranslate"><span class="pre">myfield.get_lookup('mylookup')</span></code>.</li>
<li><code class="docutils literal notranslate"><span class="pre">.filter(myfield__mytransform__mylookup)</span></code> will call
<code class="docutils literal notranslate"><span class="pre">myfield.get_transform('mytransform')</span></code>, and then
<code class="docutils literal notranslate"><span class="pre">mytransform.get_lookup('mylookup')</span></code>.</li>
<li><code class="docutils literal notranslate"><span class="pre">.filter(myfield__mytransform)</span></code> will first call
<code class="docutils literal notranslate"><span class="pre">myfield.get_lookup('mytransform')</span></code>, which will fail, so it will fall back
to calling <code class="docutils literal notranslate"><span class="pre">myfield.get_transform('mytransform')</span></code> and then
<code class="docutils literal notranslate"><span class="pre">mytransform.get_lookup('exact')</span></code>.</li>
</ul>
</div>
</div>


          </div>
        </div>
      </div>
      
        
          <div class="yui-b" id="sidebar">
            
      <div class="sphinxsidebar" role="navigation" aria-label="main navigation">
        <div class="sphinxsidebarwrapper">
  <h3><a href="../contents.html">Table of Contents</a></h3>
  <ul>
<li><a class="reference internal" href="#">Custom Lookups</a><ul>
<li><a class="reference internal" href="#a-simple-lookup-example">A simple lookup example</a></li>
<li><a class="reference internal" href="#a-simple-transformer-example">A simple transformer example</a></li>
<li><a class="reference internal" href="#writing-an-efficient-abs-lt-lookup">Writing an efficient <code class="docutils literal notranslate"><span class="pre">abs__lt</span></code> lookup</a></li>
<li><a class="reference internal" href="#a-bilateral-transformer-example">A bilateral transformer example</a></li>
<li><a class="reference internal" href="#writing-alternative-implementations-for-existing-lookups">Writing alternative implementations for existing lookups</a></li>
<li><a class="reference internal" href="#how-django-determines-the-lookups-and-transforms-which-are-used">How Django determines the lookups and transforms which are used</a></li>
</ul>
</li>
</ul>

  <h4>Previous topic</h4>
  <p class="topless"><a href="custom-model-fields.html"
                        title="previous chapter">Writing custom model fields</a></p>
  <h4>Next topic</h4>
  <p class="topless"><a href="custom-template-tags.html"
                        title="next chapter">Custom template tags and filters</a></p>
  <div role="note" aria-label="source link">
    <h3>This Page</h3>
    <ul class="this-page-menu">
      <li><a href="../_sources/howto/custom-lookups.txt"
            rel="nofollow">Show Source</a></li>
    </ul>
   </div>
<div id="searchbox" style="display: none" role="search">
  <h3>Quick search</h3>
    <div class="searchformwrapper">
    <form class="search" action="../search.html" method="get">
      <input type="text" name="q" />
      <input type="submit" value="Go" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    </div>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>
              <h3>Last update:</h3>
              <p class="topless">Jun 03, 2019</p>
          </div>
        
      
    </div>

    <div id="ft">
      <div class="nav">
    &laquo; <a href="custom-model-fields.html" title="Writing custom model fields">previous</a>
     |
    <a href="index.html" title="“How-to” guides" accesskey="U">up</a>
   |
    <a href="custom-template-tags.html" title="Custom template tags and filters">next</a> &raquo;</div>
    </div>
  </div>

      <div class="clearer"></div>
    </div>
  </body>
</html>